package com.csjy.migration.hnnd.business;

import com.csjy.migration.hnnd.*;
import com.csjy.migration.hnnd.annotation.BusinessDataConverter;
import com.csjy.migration.hnnd.dict.Bank;
import com.csjy.migration.hnnd.dict.RepayType;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * BT_ITEM_ACCOUNT_DETAIL == > nd_responsibility_loan
 * 项目台账管理( 担保责任放款记录表)
 */
@BusinessDataConverter(
        insertSql = "INSERT INTO ND_RESPONSIBILITY_LOAN(ID, BPM_STATUS, CREATE_NAME, CREATE_BY, UPDATE_NAME, UPDATE_BY, SYS_ORG_CODE, SYS_COMPANY_CODE, CREATE_DATE, UPDATE_DATE, PRO_ID, PRO_TYPE, BANK, REPAY_RATE_MONTH, BANK_NAME, PAY_MONEY, PAY_DATE, REPAY_TYPE, ANNUAL_PREMIUM, END_DATE, MANAGER_A, MANAGER_B, MANAGER_C, GURANT_TYPE, GURANT_FILE, CREDIT_FILE, CUSTOM_BANK, PAY_ACCOUNT_NO, CHECK_SIGN, REPAY_PLAN, PAY_VOUCHER, VOUCHER_FILE_ID, ACCOUNT_REMARK, TOBERELIEVED_MONEY, APPLICATION_APPROVAL_TYPE, GUARANT_AGREE_NO, GUARANTEE_CONTRACT_NO, PAY_ANNUALLY, MAX_GUARANTEE_CONTRACT, NOTICE_OF_PAYMENT, LOAN_CONTRACT_NUMBER, ANNUAL_INTEREST_RATE, CUSTOMER_LOAN_ACCOUNT, LOAN_NUMBER, IS_VALID, LOAN_REVIEW_TIME, PROCESS_ID, LENDING_NOTICE_NUMBER, PRO_SOURCE) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
        updateSql = "UPDATE ND_RESPONSIBILITY_LOAN SET BPM_STATUS = ?, CREATE_NAME = ?, CREATE_BY = ?, UPDATE_NAME = ?, UPDATE_BY = ?, SYS_ORG_CODE = ?, SYS_COMPANY_CODE = ?, CREATE_DATE = ?, UPDATE_DATE = ?, PRO_ID = ?, PRO_TYPE = ?, BANK = ?, REPAY_RATE_MONTH = ?, BANK_NAME = ?, PAY_MONEY = ?, PAY_DATE = ?, REPAY_TYPE = ?, ANNUAL_PREMIUM = ?, END_DATE = ?, MANAGER_A = ?, MANAGER_B = ?, MANAGER_C = ?, GURANT_TYPE = ?, GURANT_FILE = ?, CREDIT_FILE = ?, CUSTOM_BANK = ?, PAY_ACCOUNT_NO = ?, CHECK_SIGN = ?, REPAY_PLAN = ?, PAY_VOUCHER = ?, VOUCHER_FILE_ID = ?, ACCOUNT_REMARK = ?, TOBERELIEVED_MONEY = ?, APPLICATION_APPROVAL_TYPE = ?, GUARANT_AGREE_NO = ?, GUARANTEE_CONTRACT_NO = ?, PAY_ANNUALLY = ?, MAX_GUARANTEE_CONTRACT = ?, NOTICE_OF_PAYMENT = ?, LOAN_CONTRACT_NUMBER = ?, ANNUAL_INTEREST_RATE = ?, CUSTOMER_LOAN_ACCOUNT = ?, LOAN_NUMBER = ?, IS_VALID = ?, LOAN_REVIEW_TIME = ?, PROCESS_ID = ?, LENDING_NOTICE_NUMBER = ?, PRO_SOURCE = ? WHERE ID = ?",
        findMigratedIdListSql = "select ID from ND_RESPONSIBILITY_LOAN where pro_id = ?",
        seq = 205
)
public class NdResponsibilityLoan extends AbstractBusiness{
    public NdResponsibilityLoan(DbUtil sourceDbUtil, DbUtil targetDbUtil, SourceBusinessData sourceData, ProgramArgument programArgument, String projectId) throws SQLException {
        super(sourceDbUtil, targetDbUtil, sourceData, programArgument, projectId);
    }

    @Override
    public void migrate() throws Exception {
        for (Map btItemAccountDetail : this.sourceData.getBtItemAccountDetailList()) {
            if(!"1".equals(btItemAccountDetail.get("ACCOUNTTYPE").toString())){
                continue;
            }
            // 只迁移已核实记录
            if(!"2".equals(btItemAccountDetail.get("CHECKFLAG").toString())) {
                continue;
            }
            String id = buildId(btItemAccountDetail);
            // 判断是否需要覆盖迁移
            int needMigratedResult = this.needMigrated(id);
            if(needMigratedResult == 0) {
                continue;
            }


            // 单条插入或更新
            if(needMigratedResult == 1) {
            this.targetDbUtil.updateByParams(updateSql,this.buildValues(btItemAccountDetail),true);
                // 批量插入或更新 和 单条插入或更新 二选一
//                this.batchUpdateValues.add(this.buildValues(btItemAccountDetail));
            } else {
            this.targetDbUtil.updateByParams(insertSql,this.buildValues(btItemAccountDetail));
                // 批量插入或更新 和 单条插入或更新 二选一
//                this.batchInsertValues.add(this.buildValues(btItemAccountDetail));
            }
        }
    }

    public String buildId(Map btItemAccountDetail){
        // 基础表数据业务主键
        String originalId = btItemAccountDetail.get("ACCOUNTNO").toString();
        return originalId;
    }


    public List buildValues(Map btItemAccountDetail) throws Exception{
        List values = new ArrayList();

        //ID
        values.add(buildId(btItemAccountDetail));
        //BPM_STATUS	流程状态
        values.add("1");
        //CREATE_NAME	创建人名称
        values.add(null);
        //CREATE_BY	创建人登录名称
        values.add(null);
        //UPDATE_NAME	更新人名称
        values.add(null);
        //UPDATE_BY	更新人登录名称
        values.add(null);
        //SYS_ORG_CODE	所属部门
        values.add(null);
        //SYS_COMPANY_CODE	所属公司
        values.add("002");
        //CREATE_DATE	创建日期
        values.add(Tool.toDate(btItemAccountDetail.get("CREATE_TIME")));
        //UPDATE_DATE	更新日期
        values.add(Tool.toDate(btItemAccountDetail.get("MODIFY_TIME")));

        //PRO_ID	项目ID
        values.add(this.projectId);
        //PRO_TYPE	账目类型 新系统暂时都是1
        values.add("1");
        //BANK	合作银行
        values.add(DictTool.getTargetDictCode(Bank.class,btItemAccountDetail.get("BANKID").toString()));
        //REPAY_RATE_MONTH	还款月利率（旧系统取值：btItemAccountDetail.get("MONTHRATE")）todo
        values.add(btItemAccountDetail.get("YEARRATE"));
        //BANK_NAME	支行名称
        values.add(btItemAccountDetail.get("BANKBRANCH"));
        //PAY_MONEY	放款额|万
        values.add(btItemAccountDetail.get("OCCURSUM"));
        //PAY_DATE	放款时间
        values.add(btItemAccountDetail.get("OCCURTIME"));
        //REPAY_TYPE	还款方式
        values.add(sourceData.getRepayType());
        //ANNUAL_PREMIUM	按年付保费
        values.add(btItemAccountDetail.get("ASSFAREWAY"));
        //END_DATE	到期时间
        values.add(btItemAccountDetail.get("ENDTIME"));
        //MANAGER_A	责任经理A
        values.add(btItemAccountDetail.get("MANAGERA"));
        //MANAGER_B	责任经理B
        values.add(btItemAccountDetail.get("MANAGERB"));
        //MANAGER_C	责任经理C
        values.add(btItemAccountDetail.get("MANAGERC"));
        //GURANT_TYPE	担保品种 新系统数据为空,暂时使用旧系统数据
        values.add(btItemAccountDetail.get("ASSURECLASS"));
        //GURANT_FILE	保证合同
        values.add(btItemAccountDetail.get("PROMCONTRACTNO"));
        //CREDIT_FILE	贷款合同
        values.add(btItemAccountDetail.get("LOANCONTRACTNO"));
        //CUSTOM_BANK	客户开户行
        values.add(DictTool.getTargetDictCode(Bank.class,btItemAccountDetail.get("CLIENTBANKID")));
        //PAY_ACCOUNT_NO	放款账号
        values.add(btItemAccountDetail.get("OCCURACCOUNT") == null || btItemAccountDetail.get("OCCURACCOUNT") == "" ?
                btItemAccountDetail.get("CLIENTBANKACCOUNT") : btItemAccountDetail.get("OCCURACCOUNT"));
        //CHECK_SIGN	核实标记 旧系统：1-待核实,2-已核实,新系统：0-未核实,1-已核实
        values.add("1".equals(btItemAccountDetail.get("CHECKFLAG").toString()) ? "0" : "1");
        //REPAY_PLAN	还款计划
        values.add(btItemAccountDetail.get("PLANFLAG"));
        //values.add(null);
        //PAY_VOUCHER	放款凭证 暂时不展示附件的原因是本地没有迁移附件数据
        values.add(btItemAccountDetail.get("ELEFILENAME"));
        //VOUCHER_FILE_ID	放款凭证附件ID
        values.add(btItemAccountDetail.get("ELEFILESTORENAME") == null ?
                null : btItemAccountDetail.get("ELEFILESTORENAME").toString().replace("-",""));
        //ACCOUNT_REMARK	账目备注
        String accountRemark = Tool.decodeUnicode(btItemAccountDetail.get("ACCOUNTMEMO"));
        accountRemark = (accountRemark == null ? "" : accountRemark)
            +"\n\n老系统其他字段信息"
            + "\n还款方式："+ btItemAccountDetail.get("RETURNWAY") + "-" + BusinessTool.getOldDictDataNameByDictCodeAndDataCode("35",btItemAccountDetail.get("RETURNWAY"))
            + " \n还款计划："+ btItemAccountDetail.get("PLANFLAG") + "-" + BusinessTool.getOldDictDataNameByDictCodeAndDataCode("46",btItemAccountDetail.get("PLANFLAG"))
            + " \n还款月利率："+ btItemAccountDetail.get("MONTHRATE");

        values.add(accountRemark);
        //TOBERELIEVED_MONEY	待解除额
        values.add(this.getToBeRelieveMoney(btItemAccountDetail.get("ACCOUNTNO").toString()));
        //APPLICATION_APPROVAL_TYPE	申请审批类型 //todo
        values.add(null);
        //GUARANT_AGREE_NO	担保协议编号
        values.add(this.sourceData.getBtItemBasic().get(0).get("ASSUCONTRACTNO"));
        //GUARANTEE_CONTRACT_NO	保证合同编号
        values.add(btItemAccountDetail.get("PROMCONTRACTNO"));
        //PAY_ANNUALLY	按年支付担保费 // todo
        values.add(btItemAccountDetail.get("ASSFAREWAY"));
        //MAX_GUARANTEE_CONTRACT	最高额度保证合同 todo
        values.add(null);
        //NOTICE_OF_PAYMENT	凭放用款通知书 todo
        values.add(null);
        //LOAN_CONTRACT_NUMBER	贷款合同编号
        values.add(btItemAccountDetail.get("LOANCONTRACTNO"));
        //ANNUAL_INTEREST_RATE	年利率
        values.add(btItemAccountDetail.get("YEARRATE"));
        //CUSTOMER_LOAN_ACCOUNT	客户放款账号
        values.add(btItemAccountDetail.get("CLIENTBANKACCOUNT"));
        //LOAN_NUMBER	放款编号
        values.add(btItemAccountDetail.get("ACCOUNTNO"));
        //IS_VALID	是否使用
        values.add("Y");
        //LOAN_REVIEW_TIME	放款复核时间
        values.add(btItemAccountDetail.get("CHECKTIME"));
        //PROCESS_ID	流程ID
        values.add( btItemAccountDetail.get("WORKFLOWNO") == null ? null : btItemAccountDetail.get("WORKFLOWNO") + "-JW");
        //LENDING_NOTICE_NUMBER	放款通知书编号 TODO
        values.add(null);
        //PRO_SOURCE	项目来源 TODO
        values.add(null);


        return values;
    }



    // 获取 待解除金额
    public Object getToBeRelieveMoney(String accountNo) throws SQLException {
        String sql = "\n" +
                "    SELECT a.occurSum - (\n" +
                "        (\n" +
                "            SELECT nvl(sum(t.occurSum), 0)\n" +
                "    FROM bt_item_account_detail t\n" +
                "    WHERE t.itemNo = a.itemNo\n" +
                "    AND t.loanAccountNo = a.accountNo\n" +
                "    AND t.checkflag = 2 \n" +
                "    AND t.accountType = 2\n" +
                "\t\t)\n" +
                "    +\n" +
                "        (\n" +
                "            SELECT nvl(sum(t.occurSum), 0)\n" +
                "    FROM bt_item_instead_account_detail t\n" +
                "    WHERE t.itemNo = a.itemNo AND t.loanAccountNo = a.accountNo\n" +
                "\t\t)\n" +
                "\t) assurSum FROM BT_ITEM_ACCOUNT_DETAIL a\n" +
                "    WHERE a.ACCOUNTNO = '" + accountNo + "'\n" ;
        List<Map> list = this.sourceDbUtil.selectByParams(sql, new ArrayList());
        if(list.size() == 0) {
            return null;
        }
        return list.get(0).get("ASSURSUM");
    }
}
